package com.lanyou.esb.service;

import java.math.BigInteger;
import java.util.ArrayList;
import java.util.Arrays;
import java.util.Calendar;
import java.util.Date;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import java.util.Set;
import java.util.TreeSet;

import javax.persistence.EntityManager;
import javax.persistence.PersistenceContext;
import javax.persistence.Query;

import org.apache.commons.lang3.StringUtils;
import org.apache.commons.lang3.time.FastDateFormat;
import org.hibernate.SQLQuery;
import org.hibernate.transform.Transformers;
import org.springframework.data.domain.Page;
import org.springframework.data.domain.PageImpl;
import org.springframework.data.domain.Pageable;
import org.springframework.stereotype.Service;

import com.lanyou.cook.common.Constants;
import com.lanyou.esb.common.CommonUtils;
import com.lanyou.esb.web.param.ServiceLogSearchParam;
import com.lanyou.esb.web.serviceLogManagement.vo.AccessLogVo;

/**
 * 服务日志服务类
 * 
 * @author Davey.wu
 */
@Service
public class AccessLogService {

	@PersistenceContext
	private EntityManager entityManager;

	private static FastDateFormat FASTDATEFORMAT_YYYYMMDDHHMMSS = FastDateFormat
			.getInstance(Constants.DATETIME_PATTERN_1);
	private static FastDateFormat FASTDATEFORMAT_MM = FastDateFormat
			.getInstance(Constants.DATETIME_PATTERN_MM);

	/**
	 * 获取指定接口，指定日期调用次数
	 * 
	 * @param ids
	 *            ：接口标识列表
	 * @param date
	 *            ：日期
	 * @return
	 */
	public Map<String, Integer> countByInterfaceIdAndDate(List<String> ids,
			Date date) {
		if (date == null) {
			return new HashMap<String, Integer>();
		}
		String idsStr = "";
		// 将接口标识列表组成字符串
		for (String id : ids) {
			if (StringUtils.isBlank(idsStr)) {
				idsStr += "\"" + id + "\"";
			} else {
				idsStr += ",\"" + id + "\"";
			}
		}
		// 格式化日期
		String dateStr = FASTDATEFORMAT_YYYYMMDDHHMMSS.format(date);
		// 获取月份
		List<String> months = getMonths(dateStr, dateStr);
		// 获取表名
		String table = CommonUtils.ACCESS_LOG_TABLE_NAME + months.get(0);
		// 组装SQL
		String sql = "SELECT COUNT(m0.IF_ID) AS amount,m0.IF_ID AS id FROM "
				+ table + " m0 " + " WHERE 1=1 ";
		// 接口标识不为空，则加入接口标识选择条件
		if (StringUtils.isNotBlank(idsStr)) {
			sql += " AND m0.IF_ID IN (" + idsStr + ") ";
		}
		sql += " AND (''=:param OR DATE_FORMAT(m0.CREATED_DATE,  '%Y%m%d') = DATE_FORMAT(:param,  '%Y%m%d')) "
				+ " GROUP BY m0.IF_ID";
		Query query = entityManager.createNativeQuery(sql);
		query.setParameter("param", dateStr);
		query.unwrap(SQLQuery.class).setResultTransformer(
				Transformers.ALIAS_TO_ENTITY_MAP);
		List rows = query.getResultList();
		Map<String, Integer> map = new HashMap<>();
		// 转换结果集
		for (Object object : rows) {
			Map row = (Map) object;
			BigInteger amount = (BigInteger) row.get("amount");
			String id = (String) row.get("id");
			map.put(id, amount.intValue());
		}
		// 没有查询结果的接口，设结果为0
		for (String id : ids) {
			if (map.get(id) == null) {
				map.put(id, 0);
			}
		}
		return map;
	}

	/**
	 * 获取系统日志
	 * 
	 * @param param
	 *            :查询条件
	 * @param pageable
	 *            :分页对象
	 * @param userId
	 *            :用户编码
	 * @return
	 */
	public Page<AccessLogVo> findServiceLogInfos(ServiceLogSearchParam param,
			Pageable pageable, String userId) {
		if (StringUtils.isBlank(userId)) {
			return new PageImpl<AccessLogVo>(new ArrayList<AccessLogVo>(0),
					pageable, 0);
		}
		// 搜索的月份范围
		List<String> months = getMonths(param.getStartDateTime(),
				param.getEndDateTime());
		if (months == null || months.isEmpty()) {
			return new PageImpl<AccessLogVo>(new ArrayList<AccessLogVo>(0),
					pageable, 0);
		}
		// 表名列表
		List<String> tables = new ArrayList<String>(months.size());
		for (String month : months) {
			// 组装表名
			tables.add(CommonUtils.ACCESS_LOG_TABLE_NAME + month);
		}
		boolean field = false;
		boolean system = false;
		boolean reqResult = StringUtils.isNotBlank(param.getReqResult());
		boolean ifName = StringUtils.isNotBlank(param.getIfName());
		boolean startDate = StringUtils.isNotBlank(param.getStartDateTime());
		boolean endDate = StringUtils.isNotBlank(param.getEndDateTime());
		// 获取总记录数
		long total = getTotalNum(tables, param, field, system, ifName,
				reqResult, startDate, endDate, userId);
		// 没有记录，返回空集
		if (total <= 0) {
			return new PageImpl<AccessLogVo>(new ArrayList<AccessLogVo>(0),
					pageable, 0);
		}
		// 设置SQL头部
		String sql = generateHeaderSQL();
		// 设置SQL主体
		sql += generateBodySQL(tables.get(0), field, system, ifName, reqResult,
				startDate, endDate);
		// 设置SQL尾部
		sql += generateTailSQL(pageable.getPageNumber(),
				pageable.getPageSize(), "");
		// 获取查询对象
		Query query = entityManager.createNativeQuery(sql);
		// 用户管理的系统
		query.setParameter("userId", userId);
		// 设置请求结果参数
		if (reqResult) {
			query.setParameter("reqResult", StringUtils.isBlank(param
					.getReqResult()) ? "" : param.getReqResult().trim());
		}
		// 设置接口名参数
		if (ifName) {
			query.setParameter("ifName",
					StringUtils.isBlank(param.getIfName()) ? "" : param
							.getIfName().trim());
		}
		// 设置开始时间参数
		if (startDate) {
			query.setParameter("startDateTime", StringUtils.isBlank(param
					.getStartDateTime()) ? "" : param.getStartDateTime().trim());
		}
		// 设置结束时间参数
		if (endDate) {
			query.setParameter("endDateTime", StringUtils.isBlank(param
					.getEndDateTime()) ? "" : param.getEndDateTime());
		}
		// 设置分页参数
		if (pageable.getPageNumber() >= 0 && pageable.getPageSize() > 1) {
			int pageNumber = pageable.getPageNumber() * pageable.getPageSize();
			int pageSize = pageable.getPageSize();
			query.setParameter("pageNumber", pageNumber);
			query.setParameter("pageSize", pageSize);
		}
		query.unwrap(SQLQuery.class).setResultTransformer(
				Transformers.ALIAS_TO_ENTITY_MAP);
		List rows = query.getResultList();
		// 没有记录，返回空集
		if (rows == null) {
			return new PageImpl<AccessLogVo>(new ArrayList<AccessLogVo>(0),
					pageable, 0);
		}
		List<AccessLogVo> result = new ArrayList<AccessLogVo>(rows.size());
		// 转换查询结果
		for (Object object : rows) {
			Map row = (Map) object;
			AccessLogVo vo = new AccessLogVo();
			vo.setPubSystemName(row.get("pubSystemName") != null ? (String) row
					.get("pubSystemName") : "");
			vo.setCallSystemName(row.get("callSystemName") != null ? (String) row
					.get("callSystemName") : "");
			vo.setIfName(row.get("ifName") != null ? (String) row.get("ifName")
					: "");
			vo.setInEsbDate(row.get("inEsbDate") != null ? (Date) row
					.get("inEsbDate") : null);
			vo.setOutEsbDate(row.get("outEsbDate") != null ? (Date) row
					.get("outEsbDate") : null);
			vo.setPlatformReqDate(row.get("platformReqDate") != null ? (Date) row
					.get("platformReqDate") : null);
			vo.setPlatformResDate(row.get("platformResDate") != null ? (Date) row
					.get("platformResDate") : null);
			vo.setReqResult(row.get("reqResult") != null ? (String) row
					.get("reqResult") : "");
			vo.setResultDesc(row.get("resultDesc") != null ? (String) row
					.get("resultDesc") : "");
			result.add(vo);
		}
		Page<AccessLogVo> page = new PageImpl<AccessLogVo>(result, pageable,
				total);
		return page;
	}

	/**
	 * 获取总记录数
	 * 
	 * @param tables
	 *            :表名列表
	 * @param param
	 *            :查询参数对象
	 * @param field
	 *            ：查询条件是否包含领域
	 * @param system
	 *            ：查询条件是否包含系统
	 * @param ifName
	 *            ：查询条件是否包含接口名称
	 * @param reqResult
	 *            ：查询条件是否包含请求结果
	 * @param startDate
	 *            ：开始时间
	 * @param endDate
	 *            ：结束时间
	 * @return
	 */
	private long getTotalNum(List<String> tables, ServiceLogSearchParam param,
			boolean field, boolean system, boolean ifName, boolean reqResult,
			boolean startDate, boolean endDate, String userId) {
		// 设置SQL主体
		String sql = generateBodySQLForTotal(tables.get(0), field, system,
				ifName, reqResult, startDate, endDate);

		Query query = entityManager.createNativeQuery(sql);
		// 用户管理的系统
		query.setParameter("userId", userId);
		// 接口名称
		if (ifName) {
			query.setParameter("ifName",
					StringUtils.isBlank(param.getIfName()) ? "" : param
							.getIfName().trim());
		}
		// 请求结果
		if (reqResult) {
			query.setParameter("reqResult", StringUtils.isBlank(param
					.getReqResult()) ? "" : param.getReqResult().trim());
		}
		// 开始时间
		if (startDate) {
			query.setParameter("startDateTime", StringUtils.isBlank(param
					.getStartDateTime()) ? "" : param.getStartDateTime().trim());
		}
		// 结束时间
		if (endDate) {
			query.setParameter("endDateTime", StringUtils.isBlank(param
					.getEndDateTime()) ? "" : param.getEndDateTime());
		}
		// 获取查询结果
		List rows = query.getResultList();
		if (rows == null || rows.size() == 0) {
			return 0;
		}
		// 转换查询结果
		BigInteger total = (BigInteger) rows.get(0);
		return total.longValue();
	}

	/**
	 * 生成SQL头部
	 * 
	 * @return
	 */
	private String generateHeaderSQL() {
		StringBuilder headerBuilder = new StringBuilder();
		// 组装SQL start=======================
		headerBuilder.append("SELECT  ");
		// 选择接口标识
		headerBuilder.append("V1.IF_ID AS ifId, ");
		// 选择发布方系统名称
		headerBuilder
				.append("(SELECT s.system_name FROM t_esb_bu_if_http h,t_cook_bu_field_system s WHERE h.if_http_id=V1.IF_ID AND h.field_system_id=s.field_system_id) AS pubSystemName, ");
		// 选择调用方系统名称
		headerBuilder
				.append("(SELECT s1.system_name FROM t_cook_bu_field_system s1 WHERE V1.IF_CALL_SYSTEM_ID=s1.field_system_id) AS callSystemName, ");
		// 选择接口名称
		headerBuilder.append("V1.IF_NAME AS ifName, ");
		// 选择进入ESB时间
		headerBuilder.append("V1.IN_ESB_DATE AS inEsbDate, ");
		// 选择出ESB时间
		headerBuilder.append("V1.OUT_ESB_DATE AS outEsbDate, ");
		// 选择平台接收请求时间
		headerBuilder.append("V1.PLATFORM_REQUEST_DATE AS platformReqDate, ");
		// 选择平台响应时间
		headerBuilder.append("V1.PLATFORM_RESPONSE_DATE AS platformResDate, ");
		// 选择结果描述
		headerBuilder.append("V1.REQUEST_RESULT_DESC AS resultDesc, ");
		// 选择请求结果
		headerBuilder.append("V1.REQUEST_RESULT AS reqResult FROM (");
		// 组装SQL end=======================
		return headerBuilder.toString();
	}

	/**
	 * 统计总记录数，生成SQL主体,按月查询
	 * 
	 * @param table
	 * @return
	 */
	private String generateBodySQLForTotal(String table, boolean field,
			boolean system, boolean ifName, boolean reqResult,
			boolean startDate, boolean endDate) {
		StringBuilder bodyBuilder = new StringBuilder();

		int num = 0;
		bodyBuilder.append("SELECT COUNT(t" + num
				+ ".ACCESS_LOG_ID) AS total  ");
		bodyBuilder.append("FROM ");
		bodyBuilder.append(table);
		bodyBuilder.append(" t" + num + " ");
		bodyBuilder.append("WHERE ");
		bodyBuilder
				.append(" EXISTS(SELECT tus.FIELD_SYSTEM_ID FROM t_cook_bu_user_system tus WHERE t"
						+ num
						+ ".FIELD_SYSTEM_ID = tus.FIELD_SYSTEM_ID AND tus.USER_ID =:userId) ");
		// 接口名称
		if (ifName) {
			bodyBuilder.append("AND t" + num + ".IF_NAME LIKE :ifName ");
		}
		// 请求结果
		if (reqResult) {
			bodyBuilder.append("AND t" + num + ".REQUEST_RESULT=:reqResult ");
		}
		// 开始时间
		if (startDate) {
			bodyBuilder.append("AND t" + num
					+ ".IN_ESB_DATE >= :startDateTime ");
		}
		// 结束时间
		if (endDate) {
			bodyBuilder.append("AND t" + num + ".IN_ESB_DATE <= :endDateTime ");
		}
		// 领域
		if (field) {
			bodyBuilder.append("AND EXISTS(SELECT si" + num
					+ ".field_id FROM t_esb_bu_if_http hi" + num
					+ ",t_cook_bu_field_system si" + num + " " + " WHERE hi"
					+ num + ".if_http_id=t" + num + ".IF_ID AND hi" + num
					+ ".field_system_id=si" + num + ".field_system_id AND si"
					+ num + ".field_id=:fieldId)");
		}
		// 系统
		if (system) {
			bodyBuilder.append("AND EXISTS(SELECT hi1" + num
					+ ".field_system_id FROM t_esb_bu_if_http hi1" + num + " "
					+ " WHERE hi1" + num + ".if_http_id=t" + num
					+ ".IF_ID AND hi1" + num + ".field_system_id=:systemId)");
		}

		return bodyBuilder.toString();
	}

	/**
	 * 生成SQL主体
	 * 
	 * @param table
	 *            ：表名
	 * @return
	 */
	private String generateBodySQL(String table, boolean field, boolean system,
			boolean ifName, boolean reqResult, boolean startDate,
			boolean endDate) {
		StringBuilder bodyBuilder = new StringBuilder();
		int num = 0;
		// 组装SQL

		bodyBuilder.append("(SELECT t" + num + ".IF_ID,t" + num + ".IF_NAME,t"
				+ num + ".IF_CALL_SYSTEM_ID,t" + num + ".IN_ESB_DATE,t" + num
				+ ".OUT_ESB_DATE,t" + num + ".PLATFORM_REQUEST_DATE,t" + num
				+ ".PLATFORM_RESPONSE_DATE,t" + num + ".REQUEST_RESULT,t" + num
				+ ".REQUEST_RESULT_DESC,t" + num + ".CREATED_DATE  ");
		bodyBuilder.append("FROM ");
		bodyBuilder.append(table);
		bodyBuilder.append(" t" + num + " ");
		bodyBuilder.append("WHERE ");
		bodyBuilder
				.append(" EXISTS(SELECT tus.FIELD_SYSTEM_ID FROM t_cook_bu_user_system tus WHERE t"
						+ num
						+ ".FIELD_SYSTEM_ID = tus.FIELD_SYSTEM_ID AND tus.USER_ID =:userId) ");
		// 接口名称
		if (ifName) {
			bodyBuilder.append("AND t" + num + ".IF_NAME LIKE :ifName ");
		}
		// 请求结果
		if (reqResult) {
			bodyBuilder.append("AND t" + num + ".REQUEST_RESULT=:reqResult ");
		}
		// 开始时间
		if (startDate) {
			bodyBuilder.append("AND t" + num
					+ ".IN_ESB_DATE >= :startDateTime ");
		}
		// 结束时间
		if (endDate) {
			bodyBuilder.append("AND t" + num + ".IN_ESB_DATE <= :endDateTime ");
		}
		// 领域
		if (field) {
			bodyBuilder.append("AND EXISTS(SELECT si" + num
					+ ".field_id FROM t_esb_bu_if_http hi" + num
					+ ",t_cook_bu_field_system si" + num + " " + " WHERE hi"
					+ num + ".if_http_id=t" + num + ".IF_ID AND hi" + num
					+ ".field_system_id=si" + num + ".field_system_id AND si"
					+ num + ".field_id=:fieldId)");
		}
		// 系统
		if (system) {
			bodyBuilder.append("AND EXISTS(SELECT hi1" + num
					+ ".field_system_id FROM t_esb_bu_if_http hi1" + num + " "
					+ " WHERE hi1" + num + ".if_http_id=t" + num
					+ ".IF_ID AND hi1" + num + ".field_system_id=:systemId)");
		}
		bodyBuilder.append(")");

		return bodyBuilder.toString();
	}

	/**
	 * 生成SQL尾部
	 * 
	 * @param pageNumber
	 * @param pageSize
	 * @param sort
	 * @return
	 */
	private String generateTailSQL(int pageNumber, int pageSize, String sort) {
		String sqlTail = "";
		if (pageNumber >= 0 && pageSize > 1) {
			sqlTail += "ORDER BY CREATED_DATE DESC LIMIT :pageNumber,:pageSize";
		}
		sqlTail += " ) AS V1";
		return sqlTail;
	}

	/**
	 * 根据时间区间，获取月分集合
	 * 
	 * @param startDateTime
	 *            ：开始时间
	 * @param endDateTime
	 *            ：结束时间
	 * @return
	 */
	private List<String> getMonths(String startDateTime, String endDateTime) {
		Date startDate = null;
		Date endDate = null;
		try {
			startDate = FASTDATEFORMAT_YYYYMMDDHHMMSS.parse(startDateTime);
		} catch (Exception e) {
			startDate = null;
		}
		try {
			endDate = FASTDATEFORMAT_YYYYMMDDHHMMSS.parse(endDateTime);
		} catch (Exception e) {
			endDate = null;
		}

		Calendar startC = Calendar.getInstance();
		Calendar endC = Calendar.getInstance();
		// 设置时间
		if (startDate != null && endDate != null) {
			startC.setTime(startDate);
			endC.setTime(endDate);
		} else {
			Date date = new Date();
			startC.setTime(date);
			endC.setTime(date);
			startC.set(Calendar.MONTH, 1);
			endC.set(Calendar.MONTH, 12);
		}
		Set<String> set = new TreeSet<>();
		// 月份递增，以获取时间区间内的所有月份
		while (startC.compareTo(endC) <= 0) {
			set.add(FASTDATEFORMAT_MM.format(startC));
			startC.add(Calendar.MONTH, 1);
		}
		return Arrays.asList(set.toArray(new String[0]));
	}

}
